package com.jhg.drgclinicalpathway.dao;


import com.jhg.drgclinicalpathway.domain.V临床路径项目;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface 临床路径模板Dao {
    @Select({
            "SELECT * From V路径模板明细 a",
            "where [版本ID]=#{tplid}",
            "ORDER BY a.序号 "
    })
    public List<Map<String,Object>> 模板ById(String tplid);
    @Select({
            "SELECT * From V路径模板明细2 a",
            "where [路径ID]=#{tplid}",
            "ORDER BY a.序号 "
    })
    public List<Map<String,Object>> 模板ById2(String tplid);

    @Select({
            "SELECT 类别, SUM(金额) as 金额 From V路径模板明细 a",
            "where [版本ID]=#{tplid} and (金额 IS NOT NULL)",
            "GROUP BY 类别"
    })
    public List<Map<String,Object>> 模板费用(String tplid);

    @Select({
            "<script>",
            "SELECT id,上级id FROM dbo.LJ_临床路径项目 where 1=1 and id in",
            "<foreach collection='ls' open='(' close=')' item='id' index='i' separator=','>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    public List<V临床路径项目> 获取小分类ID(@Param("ls") List<String> ls);

    @Delete({
            "delete from LJ_临床路径明细 where 版本ID=#{id};",
            "delete from dbo.LJ_临床路径明细从表 版本ID=#{id};"
    })
    public void 删除模板明细(String id);

    @Select({
            "SELECT DISTINCT b.分组编码,b.分组名称",
            "FROM dbo.LJ_临床路径版本 a JOIN dbo.DRG权重 b ON CHARINDEX(a.场合,b.分组编码) > 0 AND b.版本 IN ( 'CHSDRG','HIS')",
            "WHERE a.路径ID = #{id} AND a.场合 IS NOT NULL ORDER BY b.分组编码"
    })
    public List<Map<String,String>> 获取分组by路径ID(String id);

    @Select({
            "SELECT * FROM dbo.DRG权重 WHERE 分组编码 = #{code} AND 版本 IN ( 'CHSDRG','HIS')"
    })
    public List<Map<String,String>> 标杆值信息by分组编码(String code);

    @Select({
            "<script>",
            "EXEC PRO_LJ_路径明细复制 @源版本ID=#{tplid},@目标路径ID=#{pid},@目标场合=#{ch},@创建人=#{doc}",
            "</script>"
    })
    public void PRO_LJ_路径明细复制(@Param("tplid") String tplid,
                                    @Param("pid") Integer pid,
                                    @Param("ch") String ch,
                                    @Param("doc") String doc
                                    );

    @Select({
            "<script>",
            "EXEC PRO_LJ_病人路径预警_EXEC @病人标识 = #{id} ",
            "</script>"
    })
    public List<Map<String,Object>> PRO_LJ_病人路径预警_EXEC(String id);

    @Select({
            "SELECT ZD.JBBM 疾病编码,ZD.ZDMS 诊断名称,(CASE ZD.zdcx WHEN 1 THEN 1 ELSE 0 END) AS 是否主诊断,PP.版本ID, (SELECT 版本说明 FROM  LJ_临床路径版本 where ID=PP.版本ID) 版本名称  ",
            ",CASE CCSTATE WHEN 1 THEN 'MCC' WHEN 2 THEN 'CC' WHEN 3 THEN '排除' ELSE NULL END 并发症状态",
            "FROM DBO.TMP_诊断信息 ZD LEFT JOIN DBO.LJ_病人路径匹配 PP ON ZD.JZH = PP.病人标识 AND ZD.PRICODE = PP.疾病私码",
            "WHERE ZD.JZH = #{id} AND ZD.JLLY = 3 AND ZD.ZDLXID = 3 order by ZD.zdcx"
    })
    public List<Map<String,Object>> 诊断2路径(String id);

    @Select({
            "EXEC PRO_LJ_路径执行情况 @版本ID = #{pid} ,@病人标识 = #{id}"
    })
    public List<Map<String,Object>> PRO_LJ_路径执行情况(@Param("pid") String pid,@Param("id") String id);

    @Select({
            "<script>",
            "SELECT TOP ${size} ${grp},COUNT(*) 总人次,SUM(是否入组) 入组人次",
            ", SUM(是否入径) 是否入径,SUM(费用超标) 费用超标",
            ",SUM(治疗不足) 治疗不足,SUM(费用极低) 费用极低,SUM(费用极高) 费用极高",
            ",SUM(费用结构不合理) 费用结构不合理",
            ",SUM(违规项目较多) 违规项目较多",
            ",SUM(并发症消耗过大) 并发症消耗过大",
            ",SUM(盈亏) 盈亏",
            ",AVG(盈亏) 人均盈亏",
            ",SUM(可优化总金额) 可优化总金额",
            ",AVG(可优化总金额) 人均可优化金额",
            "FROM V临床路径入径情况及支付风险分析",
            "WHERE ${t} BETWEEN #{stime} AND #{etime} AND ${wh}",
            "GROUP BY ${grp}",
            "ORDER BY ${order}",
            "</script>"
    })
    public List<Map<String,Object>> 支付风险分析(@Param("t") String t,
                                           @Param("stime") String stime,
                                           @Param("etime") String etime,
                                           @Param("grp") String grp,
                                           @Param("order") String order,
                                           @Param("size") String size,
                                           @Param("wh") String wh
                                           );

    @Select({
            "SELECT id,类别 FROM dbo.JC_预警信息目录 WHERE ID NOT IN (6,7,8,9,10)",
            "ORDER BY ID"
    })
    public List<Map<String,Object>> 预警信息();


    @Select({
            "<script>",
            "SELECT TOP ${size} ${grp},",
            "COUNT(1) 人次,",
            "SUM(总费用) 总费用,",
            "SUM(标准总费用) 标准总费用,",
            "SUM(标准总费用-总费用) 收入变化,",
            "SUM(可优化总金额) 可优化总金额,",
            "SUM([检验检查可优化金额]) 检验检查可优化金额,",
            "SUM([药品可优化金额]) 药品可优化金额",
            "FROM [dbo].[V亏损DRG组查询]",
            "WHERE 出院日期 BETWEEN #{stime} AND #{etime}",
            "GROUP BY ${grp}",
            "ORDER BY ${order}",
            "</script>"
    })
    public List<Map<String,Object>> DRG亏损查询(@Param("stime") String stime,
                                                @Param("etime") String etime,
                                                @Param("grp") String grp,
                                                @Param("size") Integer size,
                                                @Param("order") String order
                                                );
    @Select({
            "<script>",
            "SELECT TOP ${size} ${grp},",
            "COUNT(*) 病例数,SUM(总费用) 总费用,",
            "SUM(a.权重) 总权重,",
            "AVG(a.权重) CMI,",
            "SUM(床日) 总床日 ,SUM(药品费) 药品费,",
            "SUM(检验费) 检验费,SUM(检查费) 检查费,",
            "SUM(治疗费) 治疗费,SUM(手术费) 手术费,SUM(耗材费) 耗材费,",
            "SUM(标准总费用) 标准总费用,",
            "SUM(CAST(标准床日 AS decimal(10,4))) 标准床日,",
            "SUM(标准药品费) 标准药品费,",
            "SUM(标准检查费) 标准检查费,",
            "SUM(标准检验费) 标准检验费,",
            "SUM(ISNULL(标准手术费,0)) 标准手术费,",
            "SUM(标准耗材费) 标准耗材费,",
            "SUM(标准治疗费) 标准治疗费",
            "FROM dbo.VDCP_病人信息 a ",
            "left join V病组标杆维护 b on a.标杆来源ID=b.ID",
            //"LEFT JOIN DRG权重 c on a.病组编码=c.分组编码 AND c.版本='CHSDRG'",
            "WHERE a.标杆来源=3 ",
            "AND ${t} between #{stime} AND #{etime}",
            "<if test=\"w!=null and w!=''\">",
            "and (${w})",
            "</if>",
            "<if test=\"s!=null and s!=''\">",
            "and (b.路径名称 like '%${s}%' or b.分组编码 like '%${s}%')",
            "</if>",
            "GROUP BY ${grp}",
            "ORDER BY ${order}",
            "</script>"
    })
    public List<Map<String,Object>> 重点疾病分析(@Param("stime") String stime,
                                           @Param("etime") String etime,
                                           @Param("size") Integer size,
                                           @Param("order") String order,
                                           @Param("t") String t,
                                           @Param("s") String s,
                                                 @Param("grp") String grp,
                                                 @Param("w") String w
                                           );


    @Select({
            "<script>",
            "select top 8 医生,SUM(总费用) 总费用,SUM(标准总费用) 标准总费用,SUM(药品费) 药品费,count(*) 病例数,SUM(检验费) 检验费,sum(检查费) 检查费,sum(治疗费) 治疗费,sum([耗材费]) 耗材费,SUM([手术费]) 手术费,",
            "sum([标准药品费]) 标准药品费,sum(标准检验费) 标准检验费,sum(标准检查费) 标准检查费,",
            "sum(标准治疗费) 标准治疗费,sum([标准耗材费]) 标准耗材费,sum(标准手术费) 标准手术费,",
            "SUM(其他费) 其他费,SUM(标准其他费) 标准其他费,",
            "SUM(可优化金额) 可优化金额,SUM([并发症实际消耗])并发症金额",
            "from VDCP_病人信息_路径指标参考 where 病组编码=#{c} ",
            "AND (${t} BETWEEN  '${stime} 00:00:00' and '${etime} 23:59:59') ",
            "and 是否评估=1 and  [路径名称]=#{lp} ",
            "GROUP BY 医生",
            "ORDER BY 病例数 DESC",
            "</script>"
    })
    public List<Map<String,Object>> 重点疾病图表分析(@Param("stime") String stime,
                                                     @Param("etime") String etime,
                                                     @Param("lp") String lp,
                                                     @Param("t") String t,
                                                     @Param("c") String code);
    @Select({"<script>" ,
            "select * from V临床路径项目 where id='#{xmid}'",
            "</script>"})
    public  List<V临床路径项目> getljxm(@Param("xmid") String xmid);
}
